﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

using Data_Objects;
using Business_Objects;
namespace Data_Objects.Implementation
{
    //Ko public -> tất cả gọi qua dataacess
    class SqlServerDiemDanhDao : IDiemDanhDao
    {
        public SqlServerDiemDanhDao()
        {
            SQLUtility.TableName = "DiemDanh";
        }

        //Trả về đối tượng báo ăn
        private DiemDanh MakeDiemDanh(DataRow row)
        {
            Guid _id = Guid.Parse(row["DiemDanhID"].ToString());
            DateTime _ngayDiemDanh = DateTime.Parse(row["NgayDiemDanh"].ToString());
            Guid _studenID = Guid.Parse(row["StudenID"].ToString());
            Guid _classID = Guid.Parse(row["ClassID"].ToString());
            bool _comat = bool.Parse(row["Comat"].ToString());
            Guid _schoolYearID = Guid.Parse(row["SchoolYearID"].ToString());
            return new DiemDanh(_id, _ngayDiemDanh, _studenID, _classID, _comat, _schoolYearID);
        }

        //Lấy danh sách khách hàng trả về datatable
        private IList<DiemDanh> MakeDiemDanhs(DataTable dt)
        {
            IList<DiemDanh> list = new List<DiemDanh>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(MakeDiemDanh(row));
            }
            return list;
        }
        public IList<Business_Objects.DiemDanh> GetDiemDanh()
        {
            SQLUtility.TableName = "DiemDanh";
            DbParameter[] param = null;
            DataTable dt = CommandProvider.GetDatatable(SQLUtility.ProceduresGetAll, param);
            return MakeDiemDanhs(dt); 
        }

        public Business_Objects.DiemDanh GetDiemDanhByID(string DiemDanhID)
        {
            SQLUtility.TableName = "DiemDanh";
            DbParameter[] parameters = new DbParameter[1];
            parameters[0] = new SqlParameter("@DiemDanhID", DiemDanhID);
            DataRow row = CommandProvider.GetDataRow(SQLUtility.ProceduresGetByID, parameters);
            if (row == null) { return null; }
            else
            {
                return MakeDiemDanh(row);
            }
        }

        public void InsertDiemDanh(Business_Objects.DiemDanh DiemDanh)
        {
            SQLUtility.TableName = "DiemDanh";
            int newID = CommandProvider.Insert(SQLUtility.ProceduresInsert, Take(DiemDanh), true);
        }

        public int UpdateDiemDanh(Business_Objects.DiemDanh DiemDanh)
        {
            SQLUtility.TableName = "DiemDanh";
            try { return CommandProvider.Update(SQLUtility.ProceduresUpdate, Take(DiemDanh)); }
            catch { return -1; }
        }

        public int DeleteDiemDanh(Business_Objects.DiemDanh DiemDanh)
        {
            SQLUtility.TableName = "DiemDanh";
            try { return CommandProvider.Delete(SQLUtility.ProceduresDelete, Take(DiemDanh)); }
            catch { return -1; }
        }

        private DbParameter[] Take(DiemDanh DiemDanh)
        {
            DbParameter[] parameters = new DbParameter[6];
            parameters[0] = new SqlParameter("@DiemDanhID", DiemDanh.Id);
            parameters[1] = new SqlParameter("@NgayDiemDanh", DiemDanh.NgayDiemDanh);
            parameters[2] = new SqlParameter("@StudenID", DiemDanh.StudenID);
            parameters[3] = new SqlParameter("@ClassID", DiemDanh.ClassID);
            parameters[4] = new SqlParameter("@Comat", DiemDanh.Comat);
            parameters[5] = new SqlParameter("@SchoolYearID", DiemDanh.SchoolYearID);
            return parameters;
        }


        public List<DiemDanh> GetDiemDanhBySchoolIdAndSchoolYear(string SchoolYearID, int schoolId)
        {
            DbParameter[] parameters = new DbParameter[2];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            parameters[1] = new SqlParameter("@SchoolYearID", SchoolYearID);
            DataTable dt = CommandProvider.GetDatatable("usp_SelectDiemDanhsByAndSchoolYearIDAndSchoolID", parameters);
            return MakeDiemDanhs(dt).ToList(); 
        }


        public DiemDanh GetDiemDanhBySchoolIdAndSchoolYearAndStudentId(string SchoolYearID, int schoolId, string studentId)
        {
            DbParameter[] parameters = new DbParameter[1];
            //parameters[0] = new SqlParameter("@SchoolID", schoolId);
            //parameters[1] = new SqlParameter("@SchoolYearID", SchoolYearID);
            parameters[0] = new SqlParameter("@StudenID", studentId);
            DataRow row = CommandProvider.GetDataRow("usp_SelectDiemDanhsByAndStudenID", parameters);
            if (row == null) { return null; }
            else
            {
                return MakeDiemDanh(row);
            }
        }


        public List<DiemDanh> GetBienLaiThuByClassIdAndSchoolSchoolYear(string schoolYearID, int schoolId, string classId)
        {
            DbParameter[] parameters = new DbParameter[3];
            parameters[0] = new SqlParameter("@SchoolID", schoolId);
            parameters[1] = new SqlParameter("@SchoolYearID", schoolYearID);
            parameters[2] = new SqlParameter("@ClassID", classId);
            DataTable dt = CommandProvider.GetDatatable("usp_SelectDiemDanhsByAndSchoolIDAndSchoolYearIDInClass", parameters);
            return MakeDiemDanhs(dt).ToList();
        }
    }
}
